﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;
using CqusInfo.CR.SqlHelper;
using CqusInfo.RAL.Question;

namespace CqusInfo.DAL.Question
{
    public class Question_CommentDAL:Inteface.IQuestion_CommentDAL
    {
        private List<Question_CommentModel> qcList = new List<Question_CommentModel>();
        public Int32 GetCounts(bool isDel)
        {
            return SQLiteHelper.GetCounts("tb_Question_Comment", isDel);
        }

        /// <summary>
        /// 得到参数
        /// </summary>
        /// <param name="qcId">Id</param>
        /// <param name="model">数据集</param>
        /// <returns>parameters</returns>
        private SQLiteParameter[] GetParameters(string qcId, Question_CommentModel model)
        {
            SQLiteParameter[] parameters ={
                                         new SQLiteParameter("@qcId",DbType.String),
                                         new SQLiteParameter("@userName",DbType.String),
                                         new SQLiteParameter("@content",DbType.String),
                                         new SQLiteParameter("@dateTime",DbType.DateTime),
                                         new SQLiteParameter("@type",DbType.Int32,4)
                                         };
            parameters[0].Value = qcId == null ? Guid.NewGuid().ToString() : qcId;
            parameters[1].Value = model.c_vc_userName;
            parameters[2].Value = model.c_vc_content;
            parameters[4].Value = model.c_dt_dateTime;
            parameters[5].Value = model.c_i_type;

            return parameters;
        }

        public Int32 Add(string qId,Question_CommentModel model)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("insert into tb_Question_Comment values(");
            sqlStr.Append("c_vc_qcId=" + qId + ",c_vc_qId=@qId,c_vc_userName=@userName,c_vc_content=@content,c_dt_dateTime=@dateTime,c_i_type=@type,c_b_isDel=" + false + ")");

            return SQLiteHelper.ExecuteNonQuery(sqlStr.ToString(), GetParameters(null, model));
        }

        public Int32 Delete(string qId, bool isDel, bool trueDel)
        {
            if (trueDel)
                return SQLiteHelper.Delete("tb_Question_Comment", "c_vc_qcId", qId);
            else
                return SQLiteHelper.Delete("tb_Question_Comment", "c_vc_qcId", qId, "c_b_isDel", isDel);
        }

        /// <summary>
        /// 从数据库查找某一段时间的意见信息
        /// </summary>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <param name="isDel">true：已删除 false：未删除</param>
        /// <param name="page">页数，第几页 需要减一</param>
        /// <param name="takeItem">每页显示的数目</param>
        /// <returns>DataSet</returns>
        private DataSet Query_DB(DateTime startTime, DateTime endTime, bool isDel, Int32 page, Int32 takeItem)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select * from tb_Question_Comment limit " + (page - 1) * takeItem + "," + takeItem);
            sqlStr.Append(" where c_b_isDel=@isDel and c_dt_datetime between @startTime and @endTime");

            SQLiteParameter[] parameters ={
                                        new SQLiteParameter("@startTime",DbType.DateTime),
                                        new SQLiteParameter("@endTime",DbType.DateTime),
                                        new SQLiteParameter("@isDel",DbType.Boolean)
                                        };
            parameters[0].Value = startTime;
            parameters[1].Value = endTime;
            parameters[2].Value = isDel;

            return SQLiteHelper.ExecuteQuery(sqlStr.ToString(), parameters);
        }
        public List<Question_CommentModel> Query(DateTime startTime, DateTime endTime, bool isDel, Int32 page, Int32 takeItem)
        {
            DataSet ds = Query_DB(startTime, endTime, isDel, page, takeItem);
            string cacheId = string.Format("Question_CommentModel_{0}_{1}", startTime.ToShortDateString(), endTime.ToShortDateString());
            qcList = SqlData.GetQueryList<Question_CommentModel>(cacheId, "Question_CommentModel", ds);
            return qcList;
        }

        public List<Question_CommentModel> Query(string qId)
        {
            DataSet ds = SQLiteHelper.Query("tb_Question_Comment", "c_vc_qId", qId);
            string cacheId = string.Format("Question_CommentModel_{0}", qId);
            qcList = SqlData.GetQueryList<Question_CommentModel>(cacheId, "Question_CommentModel", ds);
            return qcList;
        }

        /// <summary>
        /// 从数据库查找某一个用户的意见信息,依据用户名查找
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <param name="qId">意见Id</param>
        /// <param name="isDel">true：已删除 false：未删除</param>
        /// <param name="page">页数，第几页 需要减一</param>
        /// <param name="takeItem">每页显示的数目</param>
        /// <returns>DataSet</returns>
        private DataSet Query_DB(string userName,string qId, bool isDel, Int32 page, Int32 takeItem)
        {
            StringBuilder sqlStr = new StringBuilder();
            
            sqlStr.Append("select * from tb_Question_Comment limit " + (page - 1) * takeItem + "," + takeItem);
            if (userName == null || qId == null)
            {
                string fieldName = userName == null && qId != null ? "c_vc_qId" : "c_vc_userName";
                sqlStr.Append(" where " + fieldName + "=@id and c_b_isDel=@isDel");
            }
            else { sqlStr.Append(" where c_vc_userName=@userName and c_vc_qId=@qId and c_b_isDel=@isDel"); }

            SQLiteParameter[] parameters ={
                                        new SQLiteParameter("@id",DbType.String),
                                        new SQLiteParameter("@userName",DbType.String),
                                        new SQLiteParameter("@qId",DbType.String),
                                        new SQLiteParameter("@isDel",DbType.Boolean)
                                        };
            parameters[0].Value = userName == null && qId != null ? qId : userName;
            parameters[1].Value = userName;
            parameters[2].Value = isDel;
            parameters[3].Value = qId;

            return SQLiteHelper.ExecuteQuery(sqlStr.ToString(), parameters);
        }
        public List<Question_CommentModel> Query(string userName,string qId, bool isDel, Int32 page, Int32 takeItem)
        {
            DataSet ds = Query_DB(userName,qId, isDel, page, takeItem);
            string cacheId = string.Format("Question_CommentModel_{0}", userName);
            qcList = SqlData.GetQueryList<Question_CommentModel>(cacheId, "Question_CommentModel", ds);
            return qcList;
        }

        public List<Question_CommentModel> Query(Int32 page, Int32 takeItem, bool isDel)
        {
            DataSet ds = SQLiteHelper.Query("tb_Question_Comment", "c_b_isDel", isDel, page, takeItem);
            string cacheId = string.Format("Question_CommentModel_{0}_{1}_{2}", page, takeItem, isDel);
            qcList = SqlData.GetQueryList<Question_CommentModel>(cacheId, "Question_CommentModel", ds);
            return qcList;
        }
    }
}
